package cn.pengpeng.hive.active;
/**
0:创建表
create table ods_app_log(....)
partitioned by(dt string)
row format delimited
fields terminated by ','

0:加载数据
load data local inpath '/root/applog/20170101' into table ods_app_log partition(dt='20170101')

1 、建一个用于存储每日活跃用户信息的表

CREATE TABLE etl_user_active_day (
    uid string
    ,commit_time string
    ,city string
    ,release_channel string
    ,app_ver_name string
    ) partitioned BY (day string);

2、抽取当日活跃用户信息，插入活跃用户信息表etl_user_active_day
** 抽取0701号的活跃用户信息 **
INSERT INTO TABLE etl_user_active_day PARTITION (day = '20170701')
SELECT uid
    ,commit_time
    ,city
    ,release_channel
    ,app_ver_name
FROM (
    SELECT uid
        ,commit_time
        ,city
        ,release_channel
        ,app_ver_name
        ,row_number() OVER (
            PARTITION BY uid ORDER BY commit_time
            ) AS rn
    FROM ods_app_log
    WHERE day = '20170701'
    ) tmp
WHERE rn = 1;


** 抽取0702号的活跃用户信息 **
INSERT INTO TABLE etl_user_active_day PARTITION (day = '20170702')
SELECT uid
    ,commit_time
    ,city
    ,release_channel
    ,app_ver_name
FROM (
    SELECT uid
        ,commit_time
        ,city
        ,release_channel
        ,app_ver_name
        ,row_number() OVER (
            PARTITION BY uid ORDER BY commit_time
            ) AS rn
    FROM ods_app_log
    WHERE day = '20170702'
    ) tmp
WHERE rn = 1;


3、统计各维度下的活跃用户数

时间       城市       渠道       版本       活跃用户数
当日        0           0         0
当日		0           0         1
当日	    0           1         0
当日	    0           1         1
当日        1           0         0
当日	    1           0         1
当日	    1           1         0
当日        1           1         1

结果表的设计：

城市,渠道,版本,活跃用户数
all  all  all   3297935
all  all  1.0    883858
all  all  2.0     78326
...
北京 小米 all     238748
北京 360  all    3284793

3.1 设计活跃用户统计结果表模型
drop table if exists dim_user_active;
create table dim_user_active(
city string,
release_channel string,
app_ver_name string,
active_user_cnt int)
partitioned by (day string,flag string);



3.2 计算各维度组合的活跃用户数据，插入结果表
multiple insert  -->  多重插入语法，阿里为hive所扩展出来的一个功能


from etl_user_active_day
 
insert into table dim_user_active partition(day='20170701',flag='000')
select 'all','all','all',count(1) 
where day='20170701'

insert into table dim_user_active partition(day='20170701',flag='001')
select 'all','all',app_ver_name,count(1) 
where day='20170701'
group by app_ver_name

insert into table dim_user_active partition(day='20170701',flag='010')
select 'all',release_channel,'all',count(1) 
where day='20170701'
group by release_channel

insert into table dim_user_active partition(day='20170701',flag='011')
select 'all',release_channel,app_ver_name,count(1) 
where day='20170701'
group by release_channel,app_ver_name

insert into table dim_user_active partition(day='20170701',flag='100')
select city,'all','all',count(1) 
where day='20170701'
group by city


insert into table dim_user_active partition(day='20170701',flag='101')
select city,'all',app_ver_name,count(1) 
where day='20170701'
group by city,app_ver_name

insert into table dim_user_active partition(day='20170701',flag='110')
select city,release_channel,'all',count(1) 
where day='20170701'
group by city,release_channel


insert into table dim_user_active partition(day='20170701',flag='111')
select city,release_channel,app_ver_name,count(1) 
where day='20170701'
group by city,release_channel,app_ver_name;
 */
public class ActiveUser {

}
